package com.itheima.dao;

import com.itheima.vo.GoodsSumVo;
import com.itheima.vo.GoodsVo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author itheima
 * @since 2021-10-25
 */
public interface GoodsDao {

    /**
     * 查询所有商品信息，
     * 包含以下字段：商品id，商品名，价格，分类名，
     * 使用商品价格进行升序排序
     *
     * @return List<GoodsVo>
     */
    @Select("SELECT goods.id,goods.goods_name AS name,goods.price,category.cate_name AS cateName FROM goods \n" +
            "LEFT JOIN category ON goods.cate_id = category.id\n" +
            "ORDER BY goods.price DESC")
    List<GoodsVo> getAll();

    /**
     * 根据分类进行分组，并统计每个分类下商品总金额
     *
     * @return List<GoodsSumVo>
     */
    @Select("SELECT category.cate_name AS cateName, g.total \n" +
            "FROM category\n" +
            "RIGHT JOIN ( SELECT SUM( goods.price ) AS total, goods.cate_id FROM goods GROUP BY cate_id ) AS g ON category.id = g.cate_id")
    List<GoodsSumVo> getByGroup();
}
